import os
import re

def load_comany():
    company = set()
    with open('company',encoding='utf-8') as fread:
        for line in fread:
            if "*" in line:
                continue
            if '有限公司' not in line:
                continue
            if '、' in line:
                continue
            line = re.sub('\(.*?\)',"",line.strip())
            company.add(line)
    return list(company)


def load_project():
    project = set()
    with open('project', encoding='utf-8') as fread:
        for line in fread:
            if "*" in line:
                continue
            line = re.sub('\(.*?\)', "", line.strip())
            line = re.sub('（.*?）', "", line.strip())
            project.add(line)
    return list(project)

def load_project_major():
    project_major = set()
    with open('project_major', encoding='utf-8') as fread:
        for line in fread:
            if "*" in line:
                continue
            if len(line.strip()) > 3:
                continue
            line = re.sub('\(.*?\)', "", line.strip())
            line = re.sub('（.*?）', "", line.strip())
            project_major.add(line)
    return list(project_major)


def load_salors(project_major):
    salors = set()
    with open('salors', encoding='utf-8') as fread:
        for line in fread:
            if "*" in line:
                continue
            if len(line.strip()) > 3:
                continue
            line = re.sub('\(.*?\)', "", line.strip())
            line = re.sub('（.*?）', "", line.strip())

            if line not in project_major:
                salors.add(line)
    return list(salors)


import random
from db_utils.mysql_utils import default_connect
from db_utils.mysql_utils import MysqlInstance
default_connect['database'] = 'demo_knowbase'

def update_comany_info(comany):
    mysql = MysqlInstance(default_connect)
    for  name in comany:
        result = mysql.search_info_from_mysql('company',{"name":name})
        if not result:
            mysql.insert_into_table('company',{"name":name})


def update_project_info(project,announce_type):
    mysql = MysqlInstance(default_connect)
    for name in project:
        result = mysql.search_info_from_mysql('project', {"name": name})
        if not result:
            insert_dict = {'name':name}
            insert_dict['工程评分'] = random.randint(0,10)/10
            insert_dict['项目招标状态'] = announce_type
            mysql.insert_into_table('project', insert_dict)

def update_project_stage_info():
    stage_list = ['可研','设计','备案','审批','核准','立项','环评','监理','施工','装修','绿化','消防','安防','竣工']
    mysql = MysqlInstance(default_connect)
    result = mysql.search_info_from_mysql('project',{})
    if result:
        project_ids = [item['project_id'] for item in result]
        project_scores = [item['工程评分'] for item in result]

        for project_id,project_score in zip(project_ids,project_scores):
            random_stage_bool = [random.randint(0,1) for _ in range(len(stage_list))]
            staeg_count = random_stage_bool.count(1)
            count = 0
            for index, (stage,stage_bool) in enumerate(zip(stage_list,random_stage_bool)):
                if stage_bool == 0 :
                    continue
                count += 1
                insert_dict = {}
                insert_dict['name'] = stage
                insert_dict['project_id'] = project_id
                insert_dict['施工阶段'] = stage
                if count == staeg_count:
                    insert_dict['阶段顺序'] = -1
                else:
                    insert_dict['阶段顺序'] = index
                if float(project_score) >= 0.5:
                    insert_dict['剪叉车'] = random.randint(5, 15)
                    insert_dict['挖掘机'] = random.randint(5, 10)
                    insert_dict['租赁时间'] = random.randint(30, 100)
                else:
                    insert_dict['剪叉车'] = random.randint(0, 5)
                    insert_dict['挖掘机'] = random.randint(0, 5)
                    insert_dict['租赁时间'] = random.randint(0, 30)
                mysql.insert_into_table('project_stage', insert_dict)


def update_project_major(project_major):
    mysql = MysqlInstance(default_connect)
    for name in project_major:
        result = mysql.search_info_from_mysql('project_major', {"name": name})
        if not result:
            insert_dict = {'name':name}
            insert_dict['电话'] = "1"+ "".join([str(random.randint(0,9)) for i in range(10)])

            mysql.insert_into_table('project_major', insert_dict)


def update_project_sale(project_sale):
    mysql = MysqlInstance(default_connect)
    for name in project_sale:
        result = mysql.search_info_from_mysql('sale', {"name": name})
        if not result:
            insert_dict = {'name':name}
            mysql.insert_into_table('sale', insert_dict)

def insert_relation_company_project():
    mysql = MysqlInstance(default_connect)
    companys = mysql.search_info_from_mysql('company', {})
    project = mysql.search_info_from_mysql('project',{})
    project_ids = [item['project_id'] for item in project]
    companys_ids = [item['company_id'] for item in companys]
    for project_id in project_ids:
        # 随机选择 3到8个company
        random_num = random.randint(3,10)
        random_companys = random.sample(companys_ids,random_num)

        worktogether_majors = []
        for index, company_id  in enumerate(random_companys):

            if index ==0:
                winbind = 1
            else:
                winbind = 0
            # 从中标公司里面选个项目负责人
            sql = '''select p.project_major_id as project_major_id from project_major as p join relation_company_project_major rcpm on p.project_major_id = rcpm.project_major_id join company c on c.company_id  = rcpm .company_id  where rcpm.company_id=''' + str(company_id)
            mysql.cur.execute(sql)
            res = mysql.cur.fetchall()
            project_major_object = random.choice(list(res))
            project_major_id = project_major_object['project_major_id']
            worktogether_majors.append(project_major_id)
            mysql.insert_into_table('relation_company_project',{
                                                                'company_id': company_id,
                                                                'project_id': project_id,
                                                                'winbidding': winbind,
                                                                "project_major_id":project_major_id}
                                    )

        for i in range(len(worktogether_majors) -1):
            for j in range(i+1,len(worktogether_majors)):
                project_major_id = worktogether_majors[i]
                project_major_id_other = worktogether_majors[j]

                mysql.insert_into_table(
                        'relation_project_worktogether',
                        {
                            'project_major_id': project_major_id,
                            'worktogether_major_id': project_major_id_other
                        }
                    )







def insert_relation_company_and_major():
    mysql = MysqlInstance(default_connect)
    companys = mysql.search_info_from_mysql('company', {})
    project_majors = mysql.search_info_from_mysql('project_major',{})

    company_ids = [item['company_id'] for item in companys]
    comany_len = len(company_ids)
    project_major_ids = [item['project_major_id'] for item in project_majors]

    for index, project_major_id in enumerate(project_major_ids):
        company_id = company_ids[index % comany_len]
        mysql.insert_into_table('relation_company_project_major',{'company_id':company_id,'project_major_id':project_major_id})


def insert_relation_project_major_and_sale():
    mysql = MysqlInstance(default_connect)
    sales = mysql.search_info_from_mysql('sale', {})
    project_majors = mysql.search_info_from_mysql('project_major', {})

    project_major_ids = [item['project_major_id'] for item in project_majors]
    sale_ids = [item['sale_id'] for item in sales]

    for sale_id in sale_ids:
        random_num = random.randint(1,3)

        random_project_major_ids = random.sample(project_major_ids, random_num)
        for project_major_id in random_project_major_ids:
            mysql.insert_into_table('relation_project_major_sale',{'project_major_id':project_major_id,'sale_id':sale_id})


def insert_project_similary():
    mysql = MysqlInstance(default_connect)
    projects = mysql.search_info_from_mysql('project', {})
    project_ids = [item['project_id'] for item in projects]

    # 每个随机选3个作为相似项目
    for project_id in project_ids:
        similary_ids = random.sample(project_ids,3)
        for similary_id in similary_ids:
            if similary_id == project_id:
                continue
            mysql.insert_into_table('similary_project',{'project_id':project_id,"similary_project_id":similary_id})







if __name__ == '__main__':
    comany = load_comany()
    project = load_project()
    project_majors = load_project_major()
    project_sale = load_salors(project_majors)

    comany = comany[:300]
    # 插入关系
    update_comany_info(comany)
    update_project_info(project[:300],announce_type="中标公告")
    update_project_stage_info()
    update_project_sale(project_majors)
    update_project_major(project_sale)


    # 插入
    # 随机组合公司 和 项目负责人关系
    insert_relation_company_and_major()

    # 随机 插入公司和 项目负责人 以及 项目 关系
    insert_relation_company_project()

    # 随机组合 销售和 项目负责人 的客户关系
    #insert_relation_project_major_and_sale()


    # 插入招标阶段项目
    #update_project_info(project[300:400],announce_type="招标公告")

    # 插入项目相似度 关系
    #insert_project_similary()




